package com.weixing.mall.provider.service.impl;


import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.weixing.mall.base.constant.GlobalConstant;
import com.weixing.mall.core.enums.OrderSysTagEnum;
import com.weixing.mall.provider.api.model.enums.AgentLevelEnum;
import com.weixing.mall.provider.api.model.enums.OrderTypeEnum;
import com.weixing.mall.provider.mapper.DayReportCustomerMapper;
import com.weixing.mall.provider.model.domain.Order;
import com.weixing.mall.provider.model.enums.*;
import com.weixing.mall.provider.model.vo.report.customer.*;
import com.weixing.mall.provider.service.IDayReportCustomerService;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author jzy
 * @date 2020/6/12
 */
@Service
public class DayReportCustomerServiceImpl extends ServiceImpl<DayReportCustomerMapper, Order> implements IDayReportCustomerService {

    @Override
    public IPage<CustomerReportFinanceVo> customerFinance(Integer roleType, Long agentId,Long agentPid, Integer pRole,String startTime, String endTime, Integer orderType, Long actId, String actName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            select=select+",0 as earnProfit";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            select= select+",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit"+"";
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.belong_type="+GlobalConstant.PROFIT_BELONG_TYPE_OWN+",true,null)) as ownQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        q.orderByDesc("isOwn");
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        IPage<CustomerReportFinanceVo> pageData = baseMapper.customerFinance(page, q);
        if (pageData.getRecords().size()>0){
            //合计的数据放在数组第一个
            pageData.getRecords().add(0,customerFinanceSum(roleType,agentId,agentPid,pRole,startTime,endTime,orderType,actId,actName));
        }
        return pageData;
    }

    public CustomerReportFinanceVo customerFinanceSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Integer orderType, Long actId, String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，角色类型为客服的订单
            q.eq("agent.role_type",1);
        }else{
            if (roleType==1){
                select="agent.agent_id agentId,agent.role_type roleType" +
                        ",'合计' userRole" +
                        ",0 as earnProfit" +
                        "";
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                select="agent.agent_id,agent.role_type roleType" +
                        ",'合计' userRole" +
                        ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit" +
                        "";
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);

        return baseMapper.customerFinanceSum(q);
    }
    @Override
    public IPage<CustomerReportFinanceVo> customerFinancePerson(Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, Integer orderType, Long actId,String actName, String sortField, String sortType, Page page) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,2 as roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if(roleType!=null&&roleType==1){
            select=select+",0 as earnProfit";
        }
        if (roleType!=null&&roleType==2){
            select=select+
                    ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,-agent.need_receive))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount)))) as earnProfit" +
                    "";
        }
        q.eq("agent.agent_id",agentId);
        q.eq(agentPid!=null,"agent.agent_pid",agentPid);
        q.eq("agent.belong_type",1);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.eq(roleType!=null,"agent.role_type",roleType);
        q.select(select+
                ",1 as isOwn" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualReturnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualStockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),0)) as actualCancelAmount" +
                ",sum(if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+" and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as onlinePaid" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount),agent.need_receive),if(agent.role_type=2,if(o.order_type=3,-o.order_price,-o.order_price*agent.agent_discount),-agent.need_receive))) as actualTurnover" +
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(agent.role_type=2,agent.need_uppay,agent.need_receive),0))-sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",if(o.order_from="+ OrderFromEnum.MEMBER.getValue()+",o.order_price,0),0))) as actualHandIn" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.need_receive,0)) as moneyReceipt" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRate" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        IPage<CustomerReportFinanceVo> pageData = baseMapper.customerFinance(page, q);
        return pageData;
    }

    @Override
    public IPage<CustomerReportDetailVo> customerFinanceDetail(Integer isOwn,Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String actName,String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.sys_tag,if(agent.role_type=1,childAgent.agent_discount,childAgent2.agent_discount) childDiscount" +
                ",o.distributor_id distributorId,o.distributor_name distributorName,o.act_name,o.order_price" +
                ",o.order_status,o.buyer,o.order_sn,o.out_trade_no,o.order_type,agent.agent_discount discount,agent.agent_sn,o.shipper_name,log.operator_name confirmName,o.created_time,o.audit_time" +
                ",goods.goods_id,goods.product_id,goods.goods_img,goods.goods_name,goods.goods_sn,goods.commodity_code" +
                ",goods.style_no,goods.cat_id,goods.cat_name,goods.color,goods.size,goods.partition_name,goods.sku_desp,goods.price,goods.market_price" +
                ",goods.cost_price,goods.height,goods.weight,goods.quantity,goods.product_status" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone" +
                ",afterSale.after_sale_type,afterSale.after_sale_result,afterSale.status afterSaleStatus,afterSale.customer_remark,max(log.created_time) auditedTime" +
                ",if("+roleType+"=2,agent.agent_discount,1) auditorDiscount" +
                //",if(o.order_type=3,o.order_price,o.order_price*agent.agent_discount) discountPrice" +
                ",if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive)) as discountPrice" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",if( agent.receivor_role=2,agent.need_uppay,0),'') refundHigherAmount" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.need_receive,'') refundLowerAmount" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.payer,'') refundObjectName" +
                ",if(o.sys_tag="+OrderSysTagEnum.YTHC_TAG_505.getValue()+",agent.payer_id,'') refundObjectId" +
/*
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+" and agent.receivor_role=2,0,agent.need_uppay) refundHigherAmount" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",0,agent.need_receive) refundLowerAmount" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",null,agent.payer) refundObjectName" +
                ",if(max(log.log_type) ="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",null,agent.payer_id) refundObjectId" +
*/
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.groupBy("o.id");
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
                //没这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }else if (orderStatus.equals(8)){// 8取消和断货的总量
                q.and(i->i.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue()).or().eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue()));
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerReportDetailVo> dayReportDetail = baseMapper.getDayReportDetail(initMpPage, q);
        return dayReportDetail;
    }

    @Override
    public Map<String, Object> customerFinanceTotal(Integer isOwn,Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId,String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("sum(o.order_price) as saleAmount" +
                ",sum(if(o.order_type=3,o.order_price,if(agent.role_type=2,o.order_price*agent.agent_discount,agent.need_receive))) as actualTurnover" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.isNotNull("o.order_type");
        q.eq(orderType!=null,"o.order_type",orderType);
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        if (orderStatus!=null){
            if (orderStatus.equals(1)){//1全部总量
                q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
            }else if (orderStatus.equals(2)){//2实际总量
               //没这种情况
            }else if (orderStatus.equals(3)){//3退货总量
                q.eq("log.log_type",OrderLogTypeEnum.REFUND.getValue());
            }else if (orderStatus.equals(4)){//4断货总量
                q.eq("log.log_type",OrderLogTypeEnum.STOCK_OUT.getValue());
            }else if (orderStatus.equals(5)){//5取消总量
                q.eq("log.log_type",OrderLogTypeEnum.CANCEL.getValue());
            }else if(orderStatus.equals(6)){//6瑕疵总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.IN_DEFE.getValue());
            }else if(orderStatus.equals(7)){//7问题总量
                q.eq("afterSale.after_sale_result",AfterSaleResultEnum.PROBLEM_ORDER.getValue());
            }
        }
        if (orderStatus.equals(6)||orderStatus.equals(7)){
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(afterSale.audited_time,'%Y-%m-%d')",endTime);
        }else{
            q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
            q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        }
        //订单操作时间
        q.isNotNull("log.created_time");
        Map<String, Object> map = baseMapper.getOrderMap(q);
        return map;
    }

    @Override
    public IPage<CustomerReportDiscountVo> customerDiscount(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            select=select+
                    ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }else{
            //根据收款人的角色来筛选，以代理商分组
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
                    // .or(z->z.eq("agent.role_type",2)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            select=select+
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id=agent.agent_id or a1.parent_id=agent.agent_id) and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id=agent.agent_id  or a1.parent_id=agent.agent_id) and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }
        if (groupType!=null){
            q.groupBy("agent.agent_id","agent.role_type","agent.agent_discount");
        }else{
            return initMpPage;
        }
        q.select(select+
                ",1 as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount(initMpPage, q);
        //IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount2(initMpPage, q,roleType,startTime,endTime,agentId,agentPid);

        if(pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerDiscountSum(roleType,agentId,agentPid,pRole,startTime,endTime,discount,groupType));
        }
        return pageData;
    }

    public CustomerReportDiscountVo customerDiscountSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' as customerName";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            select=select+",0 as isOwn"+
                    //2021年1月11日
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }else{
            select=select+",agent.agent_id agentId,"+agentId+" adsAgentId,agent.role_type roleType";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
                select=select+",2 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
                select=select+",0 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id="+agentId+" or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id="+agentId+"  or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        return baseMapper.customerDiscountSum(q);
    }

    @Override
    public IPage<CustomerReportDiscountVo> customerAgentDiscount(Integer roleType, Long agentId, Long agentPid, Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType, Integer orderType, Long actId, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            select=select+
                    ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id=agent.agent_id and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }else{
            //根据收款人的角色来筛选，以代理商分组
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            // .or(z->z.eq("agent.role_type",2)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            select=select+
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id=agent.agent_id or a1.parent_id=agent.agent_id) and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id=agent.agent_id  or a1.parent_id=agent.agent_id) and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }
        if (groupType!=null){
            q.groupBy("agent.agent_id","agent.role_type","agent.agent_discount");
        }else{
            return initMpPage;
        }
        q.select(select+
                ",1 as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortType), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc(StrUtil.isEmpty(sortType), "agent.agent_level");
        q.orderByAsc(StrUtil.isEmpty(sortType), "agent.agent_id");
        IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount(initMpPage, q);
        //IPage<CustomerReportDiscountVo> pageData = baseMapper.customerDiscount2(initMpPage, q,roleType,startTime,endTime,agentId,agentPid);

        if(pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerAgentDiscountSum(roleType,agentId,agentPid,pRole,startTime,endTime,discount,groupType,orderType,actId));
        }
        return pageData;
    }

    @Override
    public List<BigDecimal> discountList() {
        return baseMapper.discountList();
    }


    public CustomerReportDiscountVo customerAgentDiscountSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, BigDecimal discount, Integer groupType, Integer orderType, Long actId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' as customerName";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            select=select+",0 as isOwn"+
                    //2021年1月11日
                    ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                    ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                    "";
        }else{
            select=select+",agent.agent_id agentId,"+agentId+" adsAgentId,agent.role_type roleType";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
                select=select+",2 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record left join amc_agent a1 on a1.id=record.agent_id where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where a1.customer_id="+agentId+" and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
                select=select+",0 as isOwn"+
                        //2021年1月11日
                        ",(select count(0) from ads_media_orderd_record record  left join amc_agent a1 on a1.id=record.agent_id where ( record.agent_id="+agentId+" or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"') as adsQuantity " +
                        ",IFNULL((select sum(ads.price) from ads_media_orderd_record record left join ads_media_advertsing ads on record.ad_id = ads.id left join amc_agent a1 on a1.id=record.agent_id  where (record.agent_id="+agentId+"  or a1.parent_id="+agentId+") and date_format(record.pay_time,'%Y-%m-%d')>='"+startTime+"' and date_format(record.pay_time,'%Y-%m-%d')<='"+endTime+"'),0) as adsAmount " +
                        "";
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as amountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" ,true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as cancelAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣方式
        q.eq(discount!=null,"agent.agent_discount",discount);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerDiscountSum(q);
    }
    @Override
    public IPage<CustomerReportDiscountPersonVo> customerDiscountPerson(Integer roleType, Long agentId,Long agentPid, String startTime, String endTime, BigDecimal discount,Page initMpPage) {
        //添加过订单记录后修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            q.eq("agent.role_type",roleType);
            q.eq("agent.agent_id",agentId);
            q.eq(agentPid!=null,"agent.agent_pid",agentPid);
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount)) as actualAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmountTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣
        q.eq(discount!=null,"agent.agent_discount",discount);
        //只筛选正式的数据
        q.eq("o.order_type",OrderTypeEnum.NORMAL.getValue());
        //订单操作时间

        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        IPage<CustomerReportDiscountPersonVo> pageData = baseMapper.customerDiscountPerson(initMpPage, q);
        return pageData;
    }

    @Override
    public IPage<CustomerReportDiscountPersonVo> customerAgentDiscountPerson(Integer roleType, Long agentId, Long agentPid, String startTime, String endTime, BigDecimal discount, Integer orderType, Long actId, Page initMpPage) {
        //添加过订单记录后修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            q.eq("agent.role_type",roleType);
            q.eq("agent.agent_id",agentId);
            q.eq(agentPid!=null,"agent.agent_pid",agentPid);
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price*agent.agent_discount,-o.order_price*agent.agent_discount)) as actualAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price*agent.agent_discount,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price*agent.agent_discount,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price*agent.agent_discount,0)) as actualCancelAmountTeam" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //折扣
        q.eq(discount!=null,"agent.agent_discount",discount);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        IPage<CustomerReportDiscountPersonVo> pageData = baseMapper.customerDiscountPerson(initMpPage, q);
        return pageData;
    }

    @Override
    public IPage<CustomerAdvertisementDetailVo> customerDiscountAds(Integer isOwn,Integer roleType, Long agentId,String plateformName
            , String chatNum, String directorName, String orderedAgent
            , String payTimeStart, String payTimeEnd, String launchDateStart, String launchDateEnd, String endOrderDateStart, String endOrderDateEnd
            ,String sortField, String sortType,Page initPage) {
        // TODO REPORT 性价比字段不知道怎样计算
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("ads.id,mediaType.media_name,platform.plateform_name,platform.chat_num,ads.page_site pageSite,ads.launch_date,ads.end_order_date,ads.price" +
                ",agent.agent_name orderedAgent,ads.staus,ads.article_title,ads.read_amount,ads.fans,sysUser.real_name directorName,record.pay_time");
        q.ge(StrUtil.isNotEmpty(payTimeStart),"date_format(record.pay_time,'%Y-%m-%d')",payTimeStart);
        q.le(StrUtil.isNotEmpty(payTimeEnd),"date_format(record.pay_time,'%Y-%m-%d')",payTimeEnd);
        q.ge(StrUtil.isNotEmpty(launchDateStart),"date_format(ads.launch_date,'%Y-%m-%d')",launchDateStart);
        q.le(StrUtil.isNotEmpty(launchDateEnd),"date_format(ads.launch_date,'%Y-%m-%d')",launchDateEnd);
        q.ge(StrUtil.isNotEmpty(endOrderDateStart),"date_format(ads.end_order_date,'%Y-%m-%d')",endOrderDateStart);
        q.le(StrUtil.isNotEmpty(endOrderDateEnd),"date_format(ads.end_order_date,'%Y-%m-%d')",endOrderDateEnd);
        q.like(StrUtil.isNotEmpty(plateformName),"platform.plateform_name",plateformName);
        q.like(StrUtil.isNotEmpty(chatNum),"platform.chat_num",chatNum);
        q.like(StrUtil.isNotEmpty(directorName),"sysUser.real_name",directorName);
        q.like(StrUtil.isNotEmpty(orderedAgent),"agent.agent_name",orderedAgent);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        if (roleType!=null){
            if(roleType==2){
                q.eq(agentId!=null,"record.agent_id",agentId);
            }
            if(roleType==1){
                q.eq(agentId!=null,"agent.customer_id",agentId);
            }
        }
        IPage<CustomerAdvertisementDetailVo> page = baseMapper.customerDiscountAds(initPage, q);
        return page;
    }

    @Override
    public IPage<CustomerSeckillDayReportVo> customerSeckill(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Long actId, String actName,String sortField, String sortType, Page initMpPage) {
        //添加过订单操作记录修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",(sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))-sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))) lowerRebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        IPage<CustomerSeckillDayReportVo> pageData = baseMapper.customerSeckill(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().forEach(i->{
                // 获取下级代理商ids
                String childAgentIds = "#";
                if (i.getRoleType()!=null&&i.getRoleType().equals(2)) {
                    childAgentIds = baseMapper.getChildAgentIds(i.getAgentId());
                }
                i.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,i.getRoleType(),i.getAgentId(),childAgentIds));
            });
            pageData.getRecords().add(0,customerSeckillSum(roleType,agentId,agentPid,pRole,startTime,endTime,actId));
        }
        return pageData;
    }

    @Override
    public IPage<SeckillReportVo> seckillReport25z(Integer roleType, Long agentId, Long agentPid, Integer pRole, String startTime, String endTime, Long actId, String actName, String sortField, String sortType, Page initMpPage) {
        //添加过订单操作记录修改
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount discount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole" +
                "";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                ",(sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))-sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0))) lowerRebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortType), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        IPage<SeckillReportVo> pageData = baseMapper.seckillReport25z(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().forEach(i->{
                // 获取下级代理商ids
                String childAgentIds = "#";
                if (i.getRoleType()!=null&&i.getRoleType().equals(2)) {
                    childAgentIds = baseMapper.getChildAgentIds(i.getAgentId());
                }
                i.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,i.getRoleType(),i.getAgentId(),childAgentIds));
            });
            pageData.getRecords().add(0,seckillReport25zSum(roleType,agentId,agentPid,pRole,startTime,endTime,actId));
        }
        return pageData;
    }

    @Override
    public IPage<SeckillReport25zDetailVo> seckillReport25zDetail(Integer isOwn, Integer roleType, Long agentId, Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String actName, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.id,goods.goods_img,o.customer_name customerName,o.distributor_name distributorName" +
                ",level1st.agent_name level1stName,level2nd.agent_name level2ndName,level3rd.agent_name level3rdName,level4th.agent_name level4thName" +
                ",o.out_trade_no,o.order_price orderPrice,o.order_status,o.sys_tag,goods.goods_sn,goods.style_no,goods.color,goods.size" +
                ",o.order_sn,goods.market_price marketPrice,address.contact_user,address.contact_phone,CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.is_pay,o.after_sale_tag,goods.goods_name,o.act_name,o.buyer,o.shipper_name,o.express_name,o.express_sn,o.created_time,o.audit_time,o.ship_time,o.updated_time" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.distributor_id,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as distributorRebate" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level1st,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level1stRebate" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level2nd,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level2ndRebate" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level3rd,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level3rdRebate" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level4th,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level4thRebate" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.groupBy("o.id");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortType),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<SeckillReport25zDetailVo> dayReportDetail = baseMapper.seckillReport25zDetail(initMpPage, q);
        return dayReportDetail;
    }

    @Override
    public Map<String, Object> seckillReport25zDetailSum(Integer isOwn, Integer roleType, Long agentId, Long agentPid, String startTime, String endTime, String role, String userName, String mobile, Integer orderType, Integer orderStatus, Long actId, String actName) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select(
                "sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as marketPriceTotal" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as orderPriceTotal" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.distributor_id,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as distributorRebateTotal" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level1st,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level1stRebateTotal" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level2nd,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level2ndRebateTotal" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level3rd,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level3rdRebateTotal" +
                ",sum(if(agent.profit_type=1 and agent.agent_id = o.level4th,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as level4thRebateTotal" +
                "");
        //如果isOwn===1，就只查自己的订单,否则就查自己和团队的
        if (isOwn!=null&&isOwn.equals(1)){
            q.eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN);
        }
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.and(roleType!=null,i->i.eq("agent.agent_id",agentId).eq("agent.role_type",roleType).eq(agentPid!=null,"agent.agent_pid",agentPid));
        q.eq(roleType==null,"agent.role_type",1);
        q.eq(role!=null,"o.order_from",role);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(userName),"o.buyer",userName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        Map<String, Object> map = baseMapper.seckillReport25zDetailSum(q);
        return map;
    }

    /**
     * 2.5折秒杀财务日报合计
     * @param roleType
     * @param agentId
     * @param startTime
     * @param endTime
     * @param actId
     * @return
     */
    public SeckillReportVo seckillReport25zSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Long actId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            select="'合计' userRole";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        SeckillReportVo vo = baseMapper.seckillReport25zSum(q);
        // 获取下级代理商ids
        String childAgentIds = "#";
        if (roleType!=null&&roleType.equals(2)) {
            childAgentIds = baseMapper.getChildAgentIds(agentId);
        }
        vo.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,roleType,agentId,childAgentIds));
        if (roleType==null){
           // 统计未认领的单子，断货，取消，退货。
            SeckillReportVo seckillReportVo = noClaimQuantitySum(OrderTypeEnum.SECKILL.getValue(), startTime, endTime, actId);
            vo.setNoClaimQuantity(seckillReportVo.getNoClaimQuantity());
            vo.setNoClaimAmountMarket(seckillReportVo.getNoClaimAmountMarket());
            vo.setNoClaimActualAmount(seckillReportVo.getNoClaimActualAmount());
            vo.setNoClaimReturnQuantity(seckillReportVo.getNoClaimReturnQuantity());
            vo.setNoClaimReturnAmountMarket(seckillReportVo.getNoClaimReturnAmountMarket());
            vo.setNoClaimReturnActualAmount(seckillReportVo.getNoClaimReturnActualAmount());
            vo.setNoClaimStockoutQuantity(seckillReportVo.getNoClaimStockoutQuantity());
            vo.setNoClaimStockoutAmountMarket(seckillReportVo.getNoClaimStockoutAmountMarket());
            vo.setNoClaimStockoutActualAmount(seckillReportVo.getNoClaimStockoutActualAmount());
            vo.setNoClaimCancelQuantity(seckillReportVo.getNoClaimCancelQuantity());
            vo.setNoClaimCancelAmountMarket(seckillReportVo.getNoClaimCancelAmountMarket());
            vo.setNoClaimCancelActualAmount(seckillReportVo.getNoClaimCancelActualAmount());
        }
        return vo;
    }
    // 统计未认领的订单
    public SeckillReportVo noClaimQuantitySum(Integer orderType, String startTime, String endTime, Long actId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("count(0) as noClaimQuantity" +
                ",sum(goods.market_price) as noClaimAmountMarket" +
                ",sum(o.order_price) as noClaimActualAmount" +
                ",count(if(afterSale.after_sale_type = "+ AfterSalesTypeEnum.RETURN_GOODS.getValue()+",true,null)) as noClaimReturnQuantity" +
                ",sum(if(afterSale.after_sale_type = "+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",goods.market_price,0)) as noClaimReturnAmountMarket" +
                ",sum(if(afterSale.after_sale_type = "+AfterSalesTypeEnum.RETURN_GOODS.getValue()+",o.order_price,0)) as noClaimReturnActualAmount" +
                ",count(if(o.nosupplly_audit = "+NoSuppllyAuditEnum.TYPE_1.getValue()+",true,null)) as noClaimStockoutQuantity" +
                ",sum(if(o.nosupplly_audit = "+NoSuppllyAuditEnum.TYPE_1.getValue()+",goods.market_price,0)) as noClaimStockoutAmountMarket" +
                ",sum(if(o.nosupplly_audit = "+NoSuppllyAuditEnum.TYPE_1.getValue()+",o.order_price,0)) as noClaimStockoutActualAmount" +
                ",count(if(o.order_status="+OrderStateEnum.CANCELD.getValue()+",true,null)) as noClaimCancelQuantity" +
                ",sum(if(o.order_status="+OrderStateEnum.CANCELD.getValue()+",goods.market_price,0)) as noClaimCancelAmountMarket" +
                ",sum(if(o.order_status="+OrderStateEnum.CANCELD.getValue()+",o.order_price,0)) as noClaimCancelActualAmount" +
                "");
        // 只筛选已支付的订单
        q.eq("o.is_pay", 1);
        q.isNull("o.customer_id");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(o.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", orderType);
        q.eq(actId!=null,"o.act_id",actId);
        SeckillReportVo vo = baseMapper.noClaimQuantitySum(q);
        return vo;
    }


    /**
     * 计算秒杀活动团队返还款
     * @param startTime
     * @param endTime
     * @param roleType
     * @param agentId
     * @return
     */
    public BigDecimal sumTeamRebate(String startTime,String endTime,Integer roleType,Long agentId){
        // 获取下级代理商ids
        String childAgentIds = "#";
        if (roleType!=null&&roleType.equals(2)) {
            childAgentIds = baseMapper.getChildAgentIds(agentId);
        }
        return baseMapper.sumTeamRebate(startTime,endTime,roleType,agentId,childAgentIds);
    }

    /**
     * 秒杀财务日报合计
     * @param roleType
     * @param agentId
     * @param startTime
     * @param endTime
     * @param actId
     * @return
     */
    public CustomerSeckillDayReportVo customerSeckillSum(Integer roleType, Long agentId,Long agentPid,Integer pRole, String startTime, String endTime, Long actId) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="";
        if (roleType==null){
            select="'合计'as userRole";
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            select="'合计' userRole";
            if (roleType==1){
                //点击客服树的代理商Id和角色类型
                q.eq("agent.role_type",roleType);
                q.eq("agent.agent_id",agentId);
            }
            if (roleType==2){
                //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
                q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid)));
                if (pRole!=null){
                    select=select+",agent.agent_pid";
                }
            }
        }
        q.select(select+
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarket" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,0))as amountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))as actualPaidAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",goods.market_price,0)) as returnAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as actualReturnAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualReturnAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",goods.market_price,0)) as stockoutAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as actualStockoutAmountTeam" +
                ",count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantityTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarket" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",goods.market_price,0)) as cancelAmountMarketTeam" +
                ",sum(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0)) as actualCancelAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",if(agent.role_type=2,o.order_price,agent.need_receive),0)) as actualCancelAmountTeam" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1),0)) as actualQuantity" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price),0)) as actualAmountMarket" +
                ",sum(if(agent.belong_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price),0)) as actualAmount" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",1,-1)) as actualQuantityTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",goods.market_price,-goods.market_price)) as actualAmountMarketTeam" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,-o.order_price)) as actualAmountTeam" +
                ",IFNULL(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(agent.belong_type=1 and log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(agent.belong_type=1 and (log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+"),true,null))),0) as refundRate" +
                ",IFNULL(count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))/(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+" or log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))),0) as refundRateTeam" +
                ",sum(if(agent.belong_type=1 and agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebateSelf" +
                ",sum(if(agent.profit_type=1,if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",agent.agent_profit,-agent.agent_profit),0)) as rebate" +
                "");
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type", OrderTypeEnum.SECKILL.getValue());
        q.eq(actId!=null,"o.act_id",actId);
        CustomerSeckillDayReportVo customerSeckillDayReportVo = baseMapper.customerSeckillSum(q);
        // 获取下级代理商ids
        String childAgentIds = "#";
        if (roleType!=null&&roleType.equals(2)) {
            childAgentIds = baseMapper.getChildAgentIds(agentId);
        }
        customerSeckillDayReportVo.setRebateTeam(baseMapper.sumTeamRebate(startTime,endTime,roleType,agentId,childAgentIds));
        return customerSeckillDayReportVo;
    }
    @Override
    public IPage<CustomerUserReportVo> customerUserReport(String contactUser, String contactPhone, String startTime, String endTime
            , Integer isRepeat, String orderType, Integer isHave, Integer buyTimes, String joinStartTime, String joinEndTime
            , String sortField,String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String orderCondition = "";
        if (StrUtil.isNotEmpty(startTime)) {
            orderCondition = orderCondition + " and date_format(log.created_time,'%Y-%m-%d') >= '" + startTime +"'";
        }
        if (StrUtil.isNotEmpty(endTime)) {
            orderCondition = orderCondition + " and date_format(log.created_time,'%Y-%m-%d') <= '" + endTime+ "'";
        }
        if (StrUtil.isNotEmpty(orderType)) {
            orderCondition = orderCondition + " and oo.order_type = " + orderType;
        }
        if (isHave!=null) {
            // 10日内是否有复购  0否 1是
            String end = DateUtil.format(new Date(), "yyyy-MM-dd");
            Date startDate = DateUtils.addDays(new Date(), -10);
            String start = DateUtil.format(startDate, "yyyy-MM-dd");
            orderCondition = orderCondition + " and date_format(log.created_time,'%Y-%m-%d') >= '" + start + "' and date_format(log.created_time,'%Y-%m-%d') <= '" + end+"'";
        }
        q.select("memberReport.*");
        q.like(StrUtil.isNotEmpty(contactUser),"memberReport.contactUser",contactUser);
        q.like(StrUtil.isNotEmpty(contactPhone),"memberReport.contactPhone",contactPhone);
        q.ge(StrUtil.isNotEmpty(joinStartTime),"date_format(memberReport.joinTime,'%Y-%m-%d')",joinStartTime);
        q.le(StrUtil.isNotEmpty(joinEndTime),"date_format(memberReport.joinTime,'%Y-%m-%d')",endTime);
        q.lt(isRepeat!=null&&isRepeat==0,"memberReport.buyTimes",2);
        q.ge(isRepeat!=null&&isRepeat==1,"memberReport.buyTimes", 2);
        q.eq(buyTimes!=null&&buyTimes==0,"memberReport.buyTimes", 0);
        q.gt(buyTimes!=null&&buyTimes==1,"memberReport.buyTimes", 0);
        if (isHave!=null) {
            // 10日内是否有复购  0否 1是
            if (isHave == 0) {
                q.eq("memberReport.buyTimes", 0);
            }
            if (isHave == 1) {
                q.gt("memberReport.buyTimes", 0);
            }
        }
        return baseMapper.customerUserReport(initMpPage, q, orderCondition,isRepeat,buyTimes,isHave);
    }


    @Override
    public IPage<CustomerUserReportBuyLogVo> customerUserReportBuyLog(String contactPhone,Integer orderType,String startTime,String endTime, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.buyer_id buyerId,o.buyer,o.agent_name agentName,o.customer_name customerName," +
                "o.order_from orderFrom,o.order_sn orderSn,goods.goods_img goodsImg,goods.goods_sn,goods.goods_name goodsName,goods.sku_desp skuDesp,goods.color,goods.size," +
                "address.contact_user contactUser,address.contact_phone contactPhone,DATE_FORMAT(o.created_time,'%Y-%m-%d %H:%i:%s') orderCreateTime," +
                "CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address," +
                "o.audit_time auditTime,o.order_status orderStatus");
        q.eq(contactPhone!=null,"address.contact_phone",contactPhone);
        q.eq(orderType!=null,"o.order_type",orderType);
        //只筛选审核完成的订单
        q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerUserReportBuyLog(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillReportVo> customerSeckillReport(Integer role, String buyer, Integer orderType, Long actId, String startTime, String endTime,String actName,String mobile, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.id,o.order_sn,goods.goods_id,goods.goods_img goodsImg,goods.goods_sn goodsSn,goods.cat_name catName,goods.commodity_code commodityCode,o.order_type,o.act_name actName,goods.goods_name goodsName,goods.price" +
                ",act.discount,o.order_price discountPrice,goods.sku_desp skuDesp,goods.color,goods.size,o.buyer,o.buyer_id buyerId" +
                ",address.contact_user contactUser,address.contact_phone contactPhone,o.audit_time" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                ",o.customer_id customerId,o.customer_name customerName,o.agent_id agentId,o.agent_name agentName,o.distributor_id distributorId,o.distributor_name distributorName" +
                ",o.sys_tag orderStatus" +
                "");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");

        q.like(StrUtil.isNotEmpty(buyer),"o.buyer",buyer);
        q.eq(role!=null,"o.order_from",role);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        q.like(StrUtil.isNotEmpty(actName),"o.act_name",actName);
        q.like(StrUtil.isNotEmpty(mobile),"address.contact_phone",mobile);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerSeckillReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerReportRegularVo> customerReportRegular(String agentName, String startTime, String endTime, String sortField, String sortType,Integer orderType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.agent_id,agent.agent_name" +
                ",count(0) quantity" +
                ",sum(o.order_price) amount" +
                ",date_format(log.created_time,'%Y-%m-%d') auditTime");
        q.like(StrUtil.isNotEmpty(agentName),"agent.agent_name",agentName);
        q.eq(null != orderType,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        // 客服审核通过才显示
        q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
        q.eq("agent.agent_level",AgentLevelEnum.DEALER.getValue());
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        //默认时间倒序
        q.orderByDesc(StrUtil.isEmpty(sortField),"quantity");
        q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id");
        IPage<CustomerReportRegularVo> page = baseMapper.customerReportRegular(initMpPage, q);
        if (page.getRecords().size()>0){
            //合计的数据放在数组第一个
            page.getRecords().add(0,customerReportRegularSum(agentName,startTime,endTime,orderType));
        }
        return page;
    }
    public CustomerReportRegularVo customerReportRegularSum(String agentName, String startTime, String endTime,Integer orderType) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("'合计' as agentName" +
                ",count(0) quantity" +
                ",sum(o.order_price) amount" +
                "");
        q.like(StrUtil.isNotEmpty(agentName),"agent.agent_name",agentName);
        q.eq(null != orderType,"o.order_type",orderType);
        // 客服审核通过才显示
        q.eq("log.log_type",OrderLogTypeEnum.AUDIT_PASS.getValue());
        q.eq("agent.agent_level",AgentLevelEnum.DEALER.getValue());
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        return baseMapper.customerReportRegularSum(q);
    }

    @Override
    public IPage<CustomerRefundReportVo> customerRefundReport(Integer roleType, Long agentId,Long agentPid,Integer pRole, String userName, Long actId, Integer orderType, String startTime, String endTime,String monthTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="date_format(log.created_time,'%Y-%m-%d') dayTime,o.customer_id customerId,o.customer_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType,agent.agent_discount agentDiscount" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","o.order_type");
        }else{
            //如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId).eq("agent.agent_pid",0)));
            //如果是角色类型是代理商，就筛选 代理商自己的和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_OWN).eq("agent.role_type",2).eq(pRole!=null&&pRole==1,"agent.agent_pid",0).eq(pRole!=null&&pRole==2,"agent.agent_pid",agentPid))
                    .or(z->z.eq("agent.agent_pid",agentId).eq("agent.role_type",2).and(agentPid!=null,x->x.eq("o.customer_id",agentPid).or().eq("o.distributor_id",agentPid).or().eq("o.level1st",agentPid).or().eq("o.level2nd",agentPid).or().eq("o.level3rd",agentPid).or().eq("o.level4th",agentPid))));
            q.groupBy("date_format(log.created_time,'%Y-%m-%d')","agent.agent_id","agent.role_type","o.order_type");
        }
        q.select(select +",o.order_type" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+",1,0) as isOwn" +
                ",if(agent.agent_id="+agentId+" and agent.role_type="+roleType+" ,if("+pRole+">0,if("+pRole+"=1,0,"+agentPid+"),null),if("+roleType+"=1,0,"+agentId+")) as agentPid" +
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) as quantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) as amount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null)) as returnQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0)) as returnAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null)) as stockoutQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0)) as stockoutAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null)) as cancelQuantity" +
                ",sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0))as cancelAmount" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.IN_DEFE.getValue()+",true,null)) as flawQuantity" +
                ",count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.PROBLEM_ORDER.getValue()+",true,null)) as issueQuantity" +
                ",IFNULL(sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+" and afterSale.after_sale_result="+AfterSaleResultEnum.PROBLEM_ORDER.getValue()+",o.order_price,0)),0) as issueAmount" +
                "");
        //订单操作时间
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        q.likeRight(StrUtil.isNotEmpty(monthTime),"date_format(log.created_time, '%Y-%m-%d')",monthTime);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        q.eq(actId!=null,"o.act_id",actId);
        //不筛选分红的订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        q.orderByDesc("dayTime");
        q.orderByDesc("isOwn");
        q.orderBy(StrUtil.isNotEmpty(sortField), StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        q.orderByAsc("agent.agent_level");
        q.orderByAsc("agent.agent_id");
        return baseMapper.customerRefundReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerBuyerReportVo> customerBuyerReport(Integer roleType, Long agentId, String buyer, Integer orderType, Integer role, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("o.buyer,goods.goods_img,o.order_sn,o.out_trade_no,o.order_status,o.is_cliam,o.order_price,address.contact_user,address.contact_phone,goods.goods_sn,goods.sku_desp,goods.color,goods.size" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address" +
                "");
        q.eq(roleType!=null,"agent.role_type",roleType);
        q.eq(agentId!=null,"agent.agent_id",agentId);
        q.eq(role!=null,"o.order_from",role);
        q.eq(orderType!=null,"o.order_type",orderType);
        q.isNotNull("o.order_type");
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.like(StrUtil.isNotEmpty(buyer),"o.buyer",buyer);
        q.groupBy("o.id");
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerBuyerReport(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillRebateVo> customerSeckillRebate(Integer roleType, Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String  select="agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type as roleType" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            // 如果是角色类型是客服，筛选客服名下代理商的数据
            q.eq(roleType==1,"agent.role_type",2).eq("agent.kefu_id",agentId);
            //如果是角色类型是代理商，筛选代理商名下代理商的数据
            q.eq(roleType==2,"agent.agent_pid",agentId).eq("agent.role_type",2);
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebateTeam" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=1  and (agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sum" +
                ",sum(if((agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sumTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateVo> pageData = baseMapper.customerSeckillRebate(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerSeckillRebateSum(roleType,agentId,userName,startTime,endTime));
        }
        return pageData;
    }

    public CustomerSeckillRebateVo customerSeckillRebateSum(Integer roleType, Long agentId, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole";
        if (agentId!=null){
            select=select+",'"+agentId+"' agentId";
        }
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
        }
        q.select(select+
                ",sum(if(agent.belong_type=1  and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebateTeam" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL1+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusOne" +
                ",sum(if(agent.bonus_type="+GlobalConstant.BONUS_LEVEL2+" and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+",agent.agent_profit,0)) as bonusTwo" +
                ",sum(if(agent.belong_type=1  and (agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sum" +
                ",sum(if((agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+" or agent.profit_type="+ GlobalConstant.PROFIT_TYPE_BONUS+"),agent.agent_profit,0)) as sumTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.customerSeckillRebateSum(q);
    }

    @Override
    public IPage<CustomerSeckillRebateAllVo> customerSeckillRebateAll(Integer roleType, Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId,agent.agent_name userName,agent.role_type roleType" +
                ",case agent.agent_level " +
                "when "+AgentLevelEnum.KEFU.getValue()+" then '"+AgentLevelEnum.KEFU.getDesp()+"' " +
                "when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
            q.groupBy("agent.agent_id","agent.role_type");
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
            q.groupBy("agent.agent_id","agent.role_type");
        }
        q.select(select+
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebateTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateAllVo> pageData = baseMapper.customerSeckillRebateAll(initMpPage, q);
        if (pageData.getRecords().size()>0){
            pageData.getRecords().add(0,customerSeckillRebateAllSum(roleType,agentId,userName,startTime,endTime));
        }
        return pageData;
    }

    public CustomerSeckillRebateAllVo customerSeckillRebateAllSum(Integer roleType, Long agentId, String userName, String startTime, String endTime) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="'合计' userRole";
        if (agentId!=null){
            select=select+",'"+agentId+"' agentId";
        }
        if (roleType==null){
            //如果角色类型为空，查出roleType=1客服的订单
            q.eq("agent.role_type",1);
        }else{
            // 如果是角色类型是客服，就筛选 客服自己下单的和客服名下代理商的数据
            q.and(roleType==1,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",1))
                    .or(z->z.eq("agent.role_type",2).eq("agent.kefu_id",agentId)));
            //如果是角色类型是代理商，就筛选 代理商自己的（代理商自己下单和所属客户下单）和代理商名下代理商的数据
            q.and(roleType==2,i->i.and(j->j.eq("agent.agent_id",agentId).eq("agent.role_type",2))
                    .or(z->z.eq("agent.agent_pid",agentId)).eq("agent.role_type",2));
        }
        q.select(select+
                ",sum(if(agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+",agent.agent_profit,0)) as rebate" +
                ",sum(if(agent.belong_type=1 and agent.profit_type="+ GlobalConstant.PROFIT_TYPE_REBATE+", agent.agent_profit,0)) as rebateTeam" +
                "");
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        return baseMapper.customerSeckillRebateAllSum( q);
    }

    @Override
    public IPage<CustomerSeckillRebateDetailVo> customerSeckillRebateDetail(Integer type, Long agentId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("goods.product_id,goods.goods_id,goods.goods_img,goods.goods_name,goods.sku_desp,goods.color,goods.size,goods.quantity" +
                ",goods.goods_sn,goods.commodity_code,goods.style_no,goods.cat_id,goods.cat_name,goods.market_price,goods.price,goods.cost_price" +
                ",goods.product_status,goods.height,goods.weight,o.out_trade_no,agent.agent_profit amount,agent.profit_ratio ratio,o.id,o.order_sn" +
                ",o.order_price,o.audit_time,o.audit_status,o.buyer,o.agent_name,o.agent_sn,o.discount,o.agent_cliam_line,o.distributor_name,o.customer_name" +
                ",o.order_from,o.order_status" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone");
        //个人返还款
        if (type.equals(1)){
            q.eq("agent.agent_id",agentId);
            q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_REBATE);
            q.eq("agent.belong_type",1);
        }
        //团队返还款
        if (type.equals(2)){
            q.eq("agent.agent_id",agentId);
            q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_REBATE);
        }
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        IPage<CustomerSeckillRebateDetailVo> pageData = baseMapper.customerSeckillRebateDetail(initMpPage, q);
        return pageData;
    }

    @Override
    public IPage<CustomerSeckillBonusOneVo> customerSeckillBonusOne(Long agentId, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId1,agent.agent_name agentName1" +
                ",team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusOne ");
        q.eq("agent.agent_id",agentId);
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        q.eq("agent.order_type",OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerSeckillBonusOne(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusTwoVo> customerSeckillBonusTwo(Long agentId, String userName1, String userName, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("agent.kefu_id customerId,agent.kefu_name customerName,agent.agent_id agentId2,agent.agent_name agentName2" +
                ",team.obligee_id1st agentId1,team.obligee1st agentName1,team.agent_id agentId,team.agent_name agentName,agent.agent_profit bonusOne ");
        q.eq("agent.agent_id",agentId);
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        q.eq("agent.order_type",OrderTypeEnum.SECKILL.getValue());
        q.like(StrUtil.isNotEmpty(userName),"agent.agent_name",userName);
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return baseMapper.customerSeckillBonusTwo(initMpPage, q);
    }

    @Override
    public IPage<CustomerSeckillBonusDetailVo> customerSeckillBonusDetail(Integer type, Long agentId, String startTime, String endTime, String sortField, String sortType, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        q.select("goods.product_id,goods.goods_id,goods.goods_img,goods.goods_name,goods.sku_desp,goods.color,goods.size,goods.quantity" +
                ",goods.goods_sn,goods.commodity_code,goods.style_no,goods.cat_id,goods.cat_name,goods.market_price,goods.price,goods.cost_price" +
                ",goods.product_status,goods.height,goods.weight,o.out_trade_no,agent.agent_profit amount,agent.profit_ratio ratio,o.id,o.order_sn" +
                ",o.order_price,o.audit_time,o.audit_status,o.buyer,o.agent_name,o.agent_sn,o.discount,o.agent_cliam_line,o.distributor_name,o.customer_name" +
                ",o.order_from,o.order_status" +
                ",(select group_concat(agent2.agent_name) from omc_order_agent agent2 where o.id=agent2.order_id and belong_type=2 group by agent2.order_id) buyerHigher" +
                ",CONCAT_WS('',address.province,address.city,address.region,address.town,address.addr_detail) address,address.contact_user,address.contact_phone");
        //一级分红
        if (type.equals(1)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL1);
        }
        //二级分红
        if (type.equals(2)){
            q.eq("agent.bonus_type",GlobalConstant.BONUS_LEVEL2);
        }
        q.eq("agent.profit_type",GlobalConstant.PROFIT_TYPE_BONUS);
        q.eq("agent.agent_id",agentId);
        q.eq("o.order_type",OrderTypeEnum.SECKILL.getValue());
        //只筛选审核完成的订单
        q.isNotNull("o.audit_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(o.audit_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(o.audit_time,'%Y-%m-%d')",endTime);
        //排序
        q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        return  baseMapper.customerSeckillBonusDetail(initMpPage, q);
    }

    @Override
    public IPage<CustomerReportAgentRankingVo> customerReportAgentRanking(Integer orderType, Long actId, Integer isTeam, String startTime, String endTime, String sortField, String sortType,String agentName, Page initMpPage) {
        QueryWrapper<Order> q = new QueryWrapper<>();
        String select="o.act_id,o.act_name,o.order_type,o.customer_id,o.customer_name,agent.agent_id,agent.agent_name userName,2 as roleType" +
                ",case agent.agent_level when "+AgentLevelEnum.DEALER.getValue()+" then '"+AgentLevelEnum.DEALER.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_1.getValue()+" then '"+AgentLevelEnum.LEVEL_1.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_2.getValue()+" then '"+AgentLevelEnum.LEVEL_2.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_3.getValue()+" then '"+AgentLevelEnum.LEVEL_3.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_4.getValue()+" then '"+AgentLevelEnum.LEVEL_4.getDesp()+"' " +
                "when "+AgentLevelEnum.LEVEL_5.getValue()+" then '"+AgentLevelEnum.LEVEL_5.getDesp()+"' else '' end userRole";
        //个人数据
        q.eq(isTeam!=null&&isTeam==1,"agent.belong_type",1);
        q.ne("agent.agent_level",AgentLevelEnum.KEFU.getValue());
        q.select(select+
                ",count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null)) quantity"+
                ",sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0)) amount"+
                ",(count(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",true,null))-count(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",true,null))) actualQuantity"+
                ",(sum(if(log.log_type="+OrderLogTypeEnum.AUDIT_PASS.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.CANCEL.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.STOCK_OUT.getValue()+",o.order_price,0))-sum(if(log.log_type="+OrderLogTypeEnum.REFUND.getValue()+",o.order_price,0))) actualAmount"
                );
        q.eq(actId!=null,"o.act_id",actId);
        q.eq(orderType!=null,"o.order_type",orderType);
        //只筛选审核完成的订单
        q.isNotNull("log.created_time");
        q.ge(StrUtil.isNotEmpty(startTime),"date_format(log.created_time,'%Y-%m-%d')",startTime);
        q.le(StrUtil.isNotEmpty(endTime),"date_format(log.created_time,'%Y-%m-%d')",endTime);
        // 排除分红订单只统计个人和团队订单
        q.ne("agent.belong_type",GlobalConstant.PROFIT_BELONG_TYPE_PARTNER);
        //排序
        if(StrUtil.isEmpty(sortField)){
          q.orderByDesc("quantity");
        }else{
            q.orderBy(StrUtil.isNotEmpty(sortField),StringUtils.equalsIgnoreCase("ASC", sortType),sortField);
        }
        q.groupBy("agent.agent_id","o.order_type","o.act_id");
        return baseMapper.customerReportAgentRanking(initMpPage, q);
    }
}
